﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LotteryVote.Entity;
using System.Data.SqlClient;

namespace LotteryVote.Data
{
    public class NumLengthDataAccess : DataBase
    {

        /// <summary>
        /// 新增号码长度.
        /// </summary>
        /// <param name="numLen">The num len.</param>
        public void InsertNumLength(NumLength numLen)
        {
            string sql = string.Format(@"INSERT INTO {0} ({1},{2},{3}) VALUES (@{1},@{2},@{3}) SELECT IDENT_CURRENT('{0}')",
                NumLength.TABLENAME, NumLength.LENNAME, NumLength.LENGTH, NumLength.DESCRIPTION);
            object id = base.ExecuteScalar(sql, new SqlParameter(NumLength.LENNAME, numLen.LenName),
                new SqlParameter(NumLength.LENGTH, numLen.Length),
                new SqlParameter(NumLength.DESCRIPTION, numLen.Description));
            numLen.LenId = Convert.ToInt32(id);
        }
        /// <summary>
        ///删除号码长度.
        /// </summary>
        /// <param name="lenId">号码Id.</param>
        public void DeleteNumLength(int lenId)
        {
            string sql = string.Format(@"DELETE {0} WHERE {1}=@{1}", NumLength.TABLENAME, NumLength.LENID);
            base.ExecuteNonQuery(sql, new SqlParameter(NumLength.LENID, lenId));
        }
        /// <summary>
        /// 删除号码长度.
        /// </summary>
        /// <param name="len">号码的长度.</param>
        public void DeleteNumLengthByLen(int len)
        {
            string sql = string.Format(@"DELETE {0} WHERE {1}=@{1}", NumLength.TABLENAME, NumLength.LENGTH);
            base.ExecuteNonQuery(sql, new SqlParameter(NumLength.LENGTH, len));
        }

        /// <summary>
        /// 添加公司支持号码长度
        /// </summary>
        /// <param name="company">The company.</param>
        /// <param name="numLen">The num len.</param>
        /// <param name="count">The count.</param>
        public void InsertCompanyTypeSupportNumLen(CompanyTypeModel companyType, NumLength numLen, int count)
        {
            string sql = string.Format(@"INSERT INTO {0} ({1},{2},{3}) VALUES (@{1},@{2},@{3}) SELECT IDENT_CURRENT('{0}')",
                CompanyTypeSupportNumLen.TABLENAME, CompanyTypeSupportNumLen.COMPANYTYPEID, CompanyTypeSupportNumLen.LENID, CompanyTypeSupportNumLen.COUNT);
            base.ExecuteNonQuery(sql, new SqlParameter(CompanyTypeSupportNumLen.COMPANYTYPEID, companyType.Id),
                new SqlParameter(CompanyTypeSupportNumLen.LENID, numLen.LenId),
                new SqlParameter(CompanyTypeSupportNumLen.COUNT, count));
        }
        /// <summary>
        ///修改公司支持号码长度.
        /// </summary>
        /// <param name="company">The company.</param>
        /// <param name="numLen">The num len.</param>
        /// <param name="count">The count.</param>
        public void UpdateCompanyTypeSupportNumLen(CompanyTypeModel companyType, NumLength numLen, int count)
        {
            string sql = string.Format(@"UPDATE {0} SET {1}=@{1} WHERE {2}=@{2} AND {3}=@{3}",
                CompanyTypeSupportNumLen.TABLENAME, CompanyTypeSupportNumLen.COUNT, CompanyTypeSupportNumLen.COMPANYTYPEID, CompanyTypeSupportNumLen.LENID);
            base.ExecuteNonQuery(sql, new SqlParameter(CompanyTypeSupportNumLen.COUNT, count), new SqlParameter(CompanyTypeSupportNumLen.COMPANYTYPEID, companyType.Id),
                new SqlParameter(CompanyTypeSupportNumLen.LENID, numLen.LenId));
        }
        /// <summary>
        ///删除公司支持号码长度
        /// </summary>
        /// <param name="company">The company.</param>
        /// <param name="numLen">The num len.</param>
        public void DeleteCompanyTypeSupportNumLen(CompanyTypeModel companyType, NumLength numLen)
        {
            string sql = string.Format(@"DELETE {0} WHERE {1}=@{1} AND {2}=@{2}",
                CompanyTypeSupportNumLen.TABLENAME, CompanyTypeSupportNumLen.COMPANYTYPEID, CompanyTypeSupportNumLen.LENID);
            base.ExecuteNonQuery(sql, new SqlParameter(CompanyTypeSupportNumLen.COMPANYTYPEID, companyType.Id),
                new SqlParameter(CompanyTypeSupportNumLen.LENID, numLen.LenId));
        }

        /// <summary>
        /// 根据号码长度Id获取号码长度记录
        /// </summary>
        /// <param name="lenId">The len id.</param>
        /// <returns></returns>
        public NumLength GetNumLength(int lenId)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1}", NumLength.TABLENAME, NumLength.LENID);
            return base.ExecuteEntity<NumLength>(sql, new SqlParameter(NumLength.LENID, lenId));
        }
        /// <summary>
        /// 根据长度获取号码长度记录.
        /// </summary>
        /// <param name="len">The len.</param>
        /// <returns></returns>
        public NumLength GetNumLegthByLength(int len)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1}", NumLength.TABLENAME, NumLength.LENGTH);
            return base.ExecuteEntity<NumLength>(sql, new SqlParameter(NumLength.LENGTH, len));
        }
        /// <summary>
        /// 根据公司Id获取该公司所支持的号码长度
        /// </summary>
        /// <param name="companyId">The company id.</param>
        /// <returns></returns>
        public IList<CompanyTypeSupportNumLen> GetSupportNumLengthByType(CompanyTypeModel companyType)
        {
            string sql = string.Format(@"SELECT * FROM {0} NL
JOIN {1} CSN ON CSN.{2}=NL.{3}
join {4} ct on ct.Id=CSN.CompanyTypeId
WHERE CSN.{5}=@{5}", NumLength.TABLENAME, CompanyTypeSupportNumLen.TABLENAME, NumLength.LENID, CompanyTypeSupportNumLen.LENID,
 CompanyTypeModel.TABLENAME, CompanyTypeSupportNumLen.COMPANYTYPEID);
            return base.ExecuteList<CompanyTypeSupportNumLen>(sql, new SqlParameter(CompanyTypeSupportNumLen.COMPANYTYPEID, companyType.Id));
        }
        /// <summary>
        /// 根据公司类型，号码长度Id获取支持号码长度信息
        /// </summary>
        /// <param name="companyTypeId">The company type id.</param>
        /// <param name="numLenId">The num len id.</param>
        /// <returns></returns>
        public CompanyTypeSupportNumLen GetSupportNumLength(int companyTypeId, int numLenId)
        {
            string sql = string.Format(@"SELECT * FROM {0} NL
JOIN {1} CSN ON CSN.LENID=NL.LENID
WHERE CSN.{2}=@{2} AND CSN.{3}=@{3}", NumLength.TABLENAME, CompanyTypeSupportNumLen.TABLENAME, CompanyTypeSupportNumLen.COMPANYTYPEID, CompanyTypeSupportNumLen.LENID);
            return base.ExecuteEntity<CompanyTypeSupportNumLen>(sql, new SqlParameter(CompanyTypeSupportNumLen.COMPANYTYPEID, companyTypeId),
                new SqlParameter(CompanyTypeSupportNumLen.LENID, numLenId));
        }
        public CompanyTypeSupportNumLen GetSupportNumLengthByLen(int companyTypeId, int numLen)
        {
            string sql = string.Format(@"SELECT * FROM {0} NL
JOIN {1} CSN ON CSN.LENID=NL.LENID
WHERE CSN.{2}=@{2} AND NL.{3}=@{3}", NumLength.TABLENAME, CompanyTypeSupportNumLen.TABLENAME, CompanyTypeSupportNumLen.COMPANYTYPEID, NumLength.LENGTH);
            return base.ExecuteEntity<CompanyTypeSupportNumLen>(sql, new SqlParameter(CompanyTypeSupportNumLen.COMPANYTYPEID, companyTypeId),
                new SqlParameter(NumLength.LENGTH, numLen));
        }
        /// <summary>
        /// 获取所有号码长度记录.
        /// </summary>
        /// <returns></returns>
        public IList<NumLength> GetAllNumLength()
        {
            string sql = string.Format(@"SELECT * FROM {0}", NumLength.TABLENAME);
            return base.ExecuteList<NumLength>(sql);
        }
    }
}
